import pandas as pd
pd.set_option('future.no_silent_downcasting', True)

from sqlalchemy import func, or_, and_, exc
from collections import defaultdict

from sqlalchemy.exc import SQLAlchemyError

from app.config.db import DBUtils
from app.model.BusinessInfoModel import BusinessInfo
from app.model.FKListModel import fklist
from app.model.SKListModel import sklist
from app.timetest.timing_decorator import timing_decorator

db_utils = DBUtils()

class OpponentCharactersServicePandas:

    def __init__(self):
        self.db = DBUtils()

    # 对手特征
    @timing_decorator
    def select_dstz_data(self, session, company_name, company_type, start_date, end_date):
        if not company_name:
            return None
        if company_type == 1:
            fk_gsmc = fklist.FKZHMC == company_name
            sk_gsmc = sklist.SKZHMC == company_name
            fk_head = fklist.SKZHMC, fklist.FKJE, fklist.FKZHMC, fklist.SCRQ
            sk_head = sklist.FKZHMC, sklist.FKJE, sklist.SKZHMC, sklist.SCRQ
            fk_col = ['SKZHMC', 'FKJE', 'FKZHMC', 'SCRQ']
            sk_col = ['FKZHMC', 'FKJE', 'SKZHMC', 'SCRQ']
        elif company_type == 2:
            fk_gsmc = fklist.FKGS == company_name
            sk_gsmc = sklist.SKGS == company_name
            fk_head = fklist.SKZHMC, fklist.FKJE, fklist.FKGS, fklist.SCRQ
            sk_head = sklist.FKZHMC, sklist.FKJE, sklist.SKGS, sklist.SCRQ
            fk_col = ['SKZHMC', 'FKJE', 'FKGS', 'SCRQ']
            sk_col = ['FKZHMC', 'FKJE', 'SKGS', 'SCRQ']
        elif company_type == 3:
            fk_gsmc = 1 == 1
            sk_gsmc = 1 == 1
            fk_head = [fklist.SKZHMC, fklist.FKJE, fklist.SCRQ]
            sk_head = [sklist.FKZHMC, sklist.FKJE, sklist.SCRQ]
            fk_col = ['SKZHMC', 'FKJE', 'SCRQ']
            sk_col = ['FKZHMC', 'FKJE', 'SCRQ']
        else:
            return None
        try:
            # **1. 一次性查询所有相关数据，减少 SQL 交互**
            query = (
                session.query(*fk_head)
                .filter(
                    fk_gsmc,
                    fklist.SCRQ.between(start_date, end_date)
                )
            )
            fk_data = pd.DataFrame(query.all(), columns=fk_col)

            query = (
                session.query(*sk_head)
                .filter(
                    sk_gsmc,
                    sklist.SCRQ.between(start_date, end_date)
                )
            )
            sk_data = pd.DataFrame(query.all(), columns=sk_col)
            # **3. 按公司名称分组并求和**
            fk_grouped_data = fk_data.groupby('SKZHMC', as_index=False)['FKJE'].sum()
            sk_grouped_data = sk_data.groupby('FKZHMC', as_index=False)['FKJE'].sum()
            # **2. 合并上游和下游数据**
            combined_data = pd.concat([fk_grouped_data, sk_grouped_data], ignore_index=True)
            if combined_data.empty:
                return None
            # **4. 计算占比**
            grouped_data = combined_data.sort_values(by='FKJE', ascending=False)
            sum_je = grouped_data['FKJE'].sum()
            grouped_data['jezb'] = grouped_data['FKJE'] / sum_je * 100 if sum_je else 0
            # **5. 获取最大交易公司**
            max_sk_company, max_je, max_fk_company, max_jezb = grouped_data.iloc[0] if not grouped_data.empty else (None, 0, 0)
            # **6. 查询最大交易公司的业务信息**
            if pd.isna(max_sk_company) and pd.notna(max_fk_company):
                max_data = session.query(BusinessInfo.QYGM, BusinessInfo.SSSF, BusinessInfo.GBHYML).filter(
                    BusinessInfo.GSMC == max_fk_company
                ).first()
            elif pd.notna(max_sk_company) and pd.isna(max_fk_company):
                max_data = session.query(BusinessInfo.QYGM, BusinessInfo.SSSF, BusinessInfo.GBHYML).filter(
                    BusinessInfo.GSMC == max_sk_company
                ).first()
            else:
                max_data = None
            result = {
                "qygm": max_data.QYGM if max_data else None,
                "jezb": f"{round(max_jezb, 2)}%",
                "sssf": max_data.SSSF if max_data else None,
                "sjhy": max_data.GBHYML if max_data else None
            }
            return result
        except SQLAlchemyError as e:
            db_utils.rollback()
            return {"error": str(e)}
        finally:
            db_utils.remove_session()

    # 对手规模情况&成员单位分布
    @timing_decorator
    def select_dsgm_cydw_data(self, session, companyName, company_type, start_date, end_date):
        if companyName is None:
            return {'cydw_info': None}
        try:
            if company_type not in range(1,4):  # 往来单位
                sy_query = session.query(sklist.FKGS, func.count(sklist.FKGS)) \
                    .select_from(sklist) \
                    .outerjoin(BusinessInfo, sklist.SKZHMC == BusinessInfo.GSMC) \
                    .filter(
                        sklist.FKZHMC == companyName,
                        sklist.SCRQ.between(start_date, end_date)
                    ).group_by(sklist.FKGS)
                xy_query = session.query(fklist.SKGS, func.count(fklist.SKGS)) \
                    .select_from(fklist) \
                    .outerjoin(BusinessInfo, fklist.FKZHMC == BusinessInfo.GSMC) \
                    .filter(
                        fklist.SKZHMC == companyName,
                        fklist.SCRQ.between(start_date, end_date)
                    ).group_by(fklist.SKGS)
            else:
                if company_type == 1:  # 成员单位
                    fk_gsmc = fklist.FKZHMC == companyName
                    sk_gsmc = sklist.SKZHMC == companyName
                elif company_type == 2:
                    fk_gsmc = fklist.FKGS == companyName
                    sk_gsmc = sklist.SKGS == companyName
                elif company_type == 3:
                    fk_gsmc = 1 == 1
                    sk_gsmc = 1 == 1
                sy_query = session.query(BusinessInfo.QYGM, func.count(BusinessInfo.QYGM)) \
                    .select_from(fklist) \
                    .outerjoin(BusinessInfo, fklist.SKZHMC == BusinessInfo.GSMC) \
                    .filter(
                    fk_gsmc,
                    fklist.SCRQ.between(start_date, end_date)
                ).group_by(BusinessInfo.QYGM)
                xy_query = session.query(BusinessInfo.QYGM, func.count(BusinessInfo.QYGM)) \
                    .select_from(sklist) \
                    .outerjoin(BusinessInfo, sklist.FKZHMC == BusinessInfo.GSMC) \
                    .filter(
                    sk_gsmc,
                    sklist.SCRQ.between(start_date, end_date)
                ).group_by(BusinessInfo.QYGM)

            # 将 SQL 查询结果转换为 DataFrame
            sy_df = pd.DataFrame(sy_query.all(), columns=['name', 'value'])
            xy_df = pd.DataFrame(xy_query.all(), columns=['name', 'value'])
            # 过滤无效数据: name为None和‘-’的情况
            sy_df = sy_df.dropna(subset=['name'])
            sy_df = sy_df[sy_df['name'] != '-']
            xy_df = xy_df.dropna(subset=['name'])
            xy_df = xy_df[xy_df['name'] != '-']
            # 计算总数
            sy_num = int(sy_df['value'].sum())
            xy_num = int(xy_df['value'].sum())
            sy_df['value'] = sy_df['value'].astype(int)
            xy_df['value'] = xy_df['value'].astype(int)
            # 转换为所需格式
            sxy_pie_chart = {
                'sy_num': sy_num,
                'sy_num_list': sy_df.to_dict('records'),
                'xy_num': xy_num,
                'xy_num_list': xy_df.to_dict('records')
            }
            # 计算所有唯一的 `name`
            all_keys = set(sy_df['name']).union(set(xy_df['name']))
            # 归一化上游、下游数据
            sy_normalized_list = [{'name': key, 'value': int(sy_df.loc[sy_df['name'] == key, 'value'].sum())} for key in
                                  all_keys]
            xy_normalized_list = [{'name': key, 'value': int(xy_df.loc[xy_df['name'] == key, 'value'].sum())} for key in
                                  all_keys]
            # 统计数据
            sxy_statistics = {
                'all_keys': list(all_keys),
                'sy_normalized_list': sy_normalized_list,
                'xy_normalized_list': xy_normalized_list
            }
            cydw_info = {
                'sxy_pie_chart': sxy_pie_chart,
                'sxy_statistics': sxy_statistics
            }
        except exc.SQLAlchemyError as e:
            db_utils.rollback()
            return {'error': str(e)}
        finally:
            db_utils.remove_session()
        return {'cydw_info': cydw_info}

    # 对手地域分布
    @timing_decorator
    def select_dyfb_data(self, session, companyName, company_type, start_date, end_date):
        # 定义中国所有省份的列表
        provinces = ['黑龙江', '吉林', '辽宁', '内蒙古', '河北', '北京', '天津', '新疆', '西藏', '青海', '甘肃', '宁夏',
                     '山西', '山东', '陕西', '河南', '江苏', '四川', '重庆', '湖北', '安徽', '上海', '贵州', '湖南',
                     '江西', '浙江', '云南', '广西', '广东', '福建', '海南', '澳门', '香港', '台湾', '南海诸岛']
        if companyName is None:
            return None
        try:
            if company_type not in range(1, 4):
                # 上游
                sy_data = session.query(BusinessInfo.SSSF, func.count(BusinessInfo.SSSF)).select_from(sklist).outerjoin(
                    BusinessInfo, sklist.SKZHMC == BusinessInfo.GSMC).filter(
                    sklist.FKZHMC == companyName, sklist.SCRQ >= start_date, sklist.SCRQ <= end_date).group_by(
                    BusinessInfo.SSSF).all()
                # 下游
                xy_data = session.query(BusinessInfo.SSSF, func.count(BusinessInfo.SSSF)).select_from(fklist).outerjoin(
                    BusinessInfo, fklist.FKZHMC == BusinessInfo.GSMC).filter(
                    fklist.SKZHMC == companyName, fklist.SCRQ >= start_date, fklist.SCRQ <= end_date).group_by(
                    BusinessInfo.SSSF).all()
            else:
                if company_type == 1:  # 成员单位
                    fk_gsmc = fklist.FKZHMC == companyName
                    sk_gsmc = sklist.SKZHMC == companyName
                elif company_type == 2:
                    fk_gsmc = fklist.FKGS == companyName
                    sk_gsmc = sklist.SKGS == companyName
                elif company_type == 3:
                    fk_gsmc = 1 == 1
                    sk_gsmc = 1 == 1
                # 获取对手省份分布
                sy_data = session.query(BusinessInfo.SSSF, func.count(BusinessInfo.SSSF)).select_from(fklist).outerjoin(
                    BusinessInfo, fklist.SKZHMC == BusinessInfo.GSMC).filter(
                    fk_gsmc, fklist.SCRQ >= start_date, fklist.SCRQ <= end_date).group_by(
                    BusinessInfo.SSSF).all()
                xy_data = session.query(BusinessInfo.SSSF, func.count(BusinessInfo.SSSF)).select_from(sklist).outerjoin(
                    BusinessInfo, sklist.FKZHMC == BusinessInfo.GSMC).filter(
                    sk_gsmc, sklist.SCRQ >= start_date, sklist.SCRQ <= end_date).group_by(
                    BusinessInfo.SSSF).all()

        except exc.SQLAlchemyError as e:
            db_utils.rollback()
            return [{'error': str(e)}]
        finally:
            db_utils.remove_session()
        # 转换数据为 Pandas DataFrame
        sy_df = pd.DataFrame(sy_data, columns=['name', 'value'])
        xy_df = pd.DataFrame(xy_data, columns=['name', 'value'])
        # 过滤无效数据
        sy_df = sy_df.dropna(subset=['name'])
        sy_df = sy_df[sy_df['name'] != '-']
        xy_df = xy_df.dropna(subset=['name'])
        xy_df = xy_df[xy_df['name'] != '-']
        # 合并数据
        merged_df = pd.concat([sy_df, xy_df]).groupby('name', as_index=False).sum()
        # 清洗 merged_df 中的地名
        merged_df['name'] = merged_df['name'].str.replace(r'(省|市|自治区|特别行政区)', '', regex=True)
        # 确保所有省份都在数据中
        all_provinces_df = pd.DataFrame({'name': provinces})
        final_df = all_provinces_df.merge(merged_df, on='name', how='left').fillna(0).infer_objects(copy=False)
        # 排序并获取 TOP5 省份
        sorted_df = final_df.sort_values(by='value', ascending=False)
        top5_df = sorted_df.head(5)
        # 返回数据
        dsdy_info = {
            'qgfb_data': final_df.to_dict(orient='records'),
            'top5_sf': top5_df['name'].tolist(),
            'top5_num': top5_df['value'].tolist()
        }
        return {'dsdy_info': dsdy_info}

    # 对手行业分布
    @timing_decorator
    def select_hyfb_data(self, session, companyName, company_type, start_date, end_date):
        if companyName is None:
            return None

        try:
            if company_type not in range(1, 4):
                sy_data = session.query(BusinessInfo.GBHYML, func.count(BusinessInfo.GBHYML)).select_from(
                    sklist).outerjoin(
                    BusinessInfo, sklist.SKZHMC == BusinessInfo.GSMC).filter(
                    sklist.FKZHMC == companyName, sklist.SCRQ >= start_date, sklist.SCRQ <= end_date).group_by(
                    BusinessInfo.GBHYML).all()
                xy_data = session.query(BusinessInfo.GBHYML, func.count(BusinessInfo.GBHYML)).select_from(
                    fklist).outerjoin(
                    BusinessInfo, fklist.FKZHMC == BusinessInfo.GSMC).filter(
                    fklist.SKZHMC == companyName, fklist.SCRQ >= start_date, fklist.SCRQ <= end_date).group_by(
                    BusinessInfo.GBHYML).all()
            else:
                if company_type == 1:  # 成员单位
                    fk_gsmc = fklist.FKZHMC == companyName
                    sk_gsmc = sklist.SKZHMC == companyName
                elif company_type == 2:
                    fk_gsmc = fklist.FKGS == companyName
                    sk_gsmc = sklist.SKGS == companyName
                elif company_type == 3:
                    fk_gsmc = 1 == 1
                    sk_gsmc = 1 == 1
                sy_data = session.query(BusinessInfo.GBHYML, func.count(BusinessInfo.GBHYML)).select_from(
                    fklist).outerjoin(
                    BusinessInfo, fklist.SKZHMC == BusinessInfo.GSMC).filter(
                    fk_gsmc, fklist.SCRQ >= start_date, fklist.SCRQ <= end_date).group_by(
                    BusinessInfo.GBHYML).all()
                xy_data = session.query(BusinessInfo.GBHYML, func.count(BusinessInfo.GBHYML)).select_from(
                    sklist).outerjoin(
                    BusinessInfo, sklist.FKZHMC == BusinessInfo.GSMC).filter(
                    sk_gsmc, sklist.SCRQ >= start_date, sklist.SCRQ <= end_date).group_by(
                    BusinessInfo.GBHYML).all()

        except exc.SQLAlchemyError as e:
            db_utils.rollback()
            return [{'error': str(e)}]
        finally:
            db_utils.remove_session()
        # 转换数据为 Pandas DataFrame
        sy_df = pd.DataFrame(sy_data, columns=['name', 'value'])
        xy_df = pd.DataFrame(xy_data, columns=['name', 'value'])
        # 过滤无效数据
        sy_df = sy_df.dropna(subset=['name'])
        sy_df = sy_df[sy_df['name'] != '-']
        xy_df = xy_df.dropna(subset=['name'])
        xy_df = xy_df[xy_df['name'] != '-']
        # 合并数据并进行聚合
        merged_df = pd.merge(sy_df, xy_df, on='name', how='outer', suffixes=('_sy', '_xy')).fillna(0)
        # 计算上游和下游总数
        sy_total = int(merged_df['value_sy'].sum())
        xy_total = int(merged_df['value_xy'].sum())
        # 饼图上游行业比例
        sy_gbhyml_num_list = merged_df[['name', 'value_sy']].rename(columns={'value_sy': 'value'}).to_dict(orient='records')
        xy_gbhyml_num_list = merged_df[['name', 'value_xy']].rename(columns={'value_xy': 'value'}).to_dict(orient='records')
        # 转换数据为字典
        sxy_pie_chart = {
            'sy_gbhyml_num': len(sy_gbhyml_num_list),
            'sy_gbhyml_num_list': sy_gbhyml_num_list,
            'xy_gbhyml_num': len(xy_gbhyml_num_list),
            'xy_gbhyml_num_list': xy_gbhyml_num_list
        }
        sxy_statistics = {
            'all_keys': merged_df['name'].tolist(),
            'sy_normalized_list': merged_df[['name', 'value_sy']].rename(columns={'value_sy': 'value'}).to_dict(orient='records'),
            'xy_normalized_list': merged_df[['name', 'value_xy']].rename(columns={'value_xy': 'value'}).to_dict(orient='records')
        }
        return {
            'dshy_info': {
                'sxy_pie_chart': sxy_pie_chart,
                'sxy_statistics': sxy_statistics
            }
        }



